﻿using DotNetDAL.Entity;
using DotNetDAL.Extensions;
using DotNetDAL.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace DotNetDAL.Connector
{
    /// <summary>
    /// Sql Server 数据连接器
    /// </summary>
    public class SqlServerConnector : IConnector, IDisposable
    {
        #region 字段

        /// <summary>
        /// 数据库连接
        /// </summary>
        private SqlConnection conn;
        private string ConnectionString;
        private SqlServerTransactor _transactor { get; set; }
        #endregion

        #region 属性

        /// <summary>
        /// 数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                if (conn == null)
                {
                    throw new Exception("数据库连接对象为 null ");
                }

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                else if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }

                return conn;
            }
        }


        private ConnectorUtils _Utils { get; set; }
        public ConnectorUtils Utils
        {
            get
            {
                if (_Utils == null)
                {
                    _Utils = new ConnectorUtils();
                }
                return _Utils;

            }
        }


        private SqlServerCacheHelper _CacheHelper { get; set; }
        public SqlServerCacheHelper CacheHelper
        {
            get
            {
                if (_CacheHelper == null)
                {
                    _CacheHelper = new SqlServerCacheHelper(Connection);
                }
                return _CacheHelper;

            }
        }







        #endregion

        #region 构造函数

        ///// <summary>
        ///// 构造函数
        ///// </summary>
        //public SqlServerConnector()
        //{
        //    string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
        //    conn = new SqlConnection(ConnectionString);
        //    conn.Open();
        //}

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串</param>
        public SqlServerConnector(string ConnectionString)
        {
            this.ConnectionString = ConnectionString;
            conn = new SqlConnection(ConnectionString);
            conn.Open();

            
        }

        #endregion

        #region IDisposable Support

        private bool disposedValue = false; // 要检测冗余调用
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (!disposedValue)
            {
                //释放数据库连接对象
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }

                disposedValue = true;
            }
        }
        #endregion

        #region 数据库连接管理

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public DbConnection GetConnection()
        {
            return Connection;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (conn == null)
            {
                throw new Exception("数据库连接对象为 null ");
            }

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }

        #endregion

        #region 创建数据库命令

        /// <summary>
        /// 创建数据库命令
        /// </summary>
        /// <param name="SQL">sql语句</param>
        /// <param name="paramObj">sql参数</param>
        /// <param name="cmdType"></param>
        /// <param name="CommandTimeout">超时时间。以秒为单位</param>
        /// <returns></returns>
        public DbCommand CreateCommand(string SQL, object paramObj, CommandType cmdType, int CommandTimeout = 60)
        {
            SqlCommand _SqlCommand = new SqlCommand();
            _SqlCommand.Connection = Connection;
            _SqlCommand.CommandType = cmdType;

            if (!string.IsNullOrEmpty(SQL))
            {
                _SqlCommand.CommandText = SQL;
            }
            //以秒为单位
            _SqlCommand.CommandTimeout = CommandTimeout;

            _SqlCommand.Parameters.Clear();
            if (paramObj != null)
            {
                List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(paramObj);
                if(paramList.Count > 0)
                {
                    _SqlCommand.Parameters.AddRange(paramList.ToArray());
                }
            }
            return _SqlCommand;
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 执行SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">执行类型</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, object paramObj = null, CommandType cmdType = CommandType.Text)
        {
            SqlCommand _SqlCommand = CreateCommand(sql, paramObj, cmdType) as SqlCommand;
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    sda.Dispose();

                    return ds;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion

        #region ExecuteDataTable

        /// <summary>
        /// 执行SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">执行类型</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, object paramObj = null, CommandType cmdType = CommandType.Text)
        {
            SqlCommand _SqlCommand = CreateCommand(sql, paramObj, cmdType) as SqlCommand;
            try
            {
                using (DataTable dt = new DataTable())
                {
                    SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
                    sda.Fill(dt);

                    sda.Dispose();

                    return dt;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sqlParameters">Sql参数</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="IsTransaction">是否使用事务</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string sql, SqlParameter[] sqlParameters = null, CommandType cmdType = CommandType.Text, bool IsTransaction = false)
        {
            if (IsTransaction)
            {
                SqlTransaction _Transaction = Connection.BeginTransaction();
                int res = 0;
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, Connection, _Transaction);
                    cmd.CommandType = cmdType;
                    cmd.Parameters.Clear();
                    if (sqlParameters != null)
                    {
                        cmd.Parameters.AddRange(sqlParameters);
                    }
                    res = cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    //提交事务
                    _Transaction.Commit();

                }
                catch (Exception exp)
                {
                    //回滚事务
                    _Transaction.Rollback();
                    throw exp;
                }

                _Transaction.Dispose();
                return res;
            }
            else
            {
                SqlCommand _SqlCommand = new SqlCommand(sql, Connection);
                _SqlCommand.CommandType = cmdType;
                _SqlCommand.Parameters.Clear();
                if (sqlParameters != null)
                {
                    _SqlCommand.Parameters.AddRange(sqlParameters);
                }
                try
                {
                    return _SqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    _SqlCommand.Dispose();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="IsTransaction">是否使用事务</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string sql, object paramObj = null, CommandType cmdType = CommandType.Text, bool IsTransaction = false)
        {
            if (IsTransaction)
            {
                SqlTransaction _Transaction = Connection.BeginTransaction();
                int res = 0;
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, Connection, _Transaction);
                    cmd.CommandType = cmdType;
                    cmd.Parameters.Clear();
                    if (paramObj != null)
                    {
                        List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(paramObj);
                        if (paramList.Count > 0)
                        {
                            cmd.Parameters.AddRange(paramList.ToArray());
                        }
                    }
                    res = cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    //提交事务
                    _Transaction.Commit();

                }
                catch (Exception exp)
                {
                    //回滚事务
                    _Transaction.Rollback();
                    throw exp;
                }

                _Transaction.Dispose();
                return res;
            }
            else
            {
                SqlCommand _SqlCommand = CreateCommand(sql, paramObj, cmdType) as SqlCommand;
                try
                {
                    return _SqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    _SqlCommand.Dispose();
                }
            }
        }

        /// <summary>
        /// 使用事务，批量执行SQL语句，返回受影响的行数
        /// </summary>
        /// <param name="pars">执行参数对象</param>
        /// <returns></returns>
        public int ExecuteNonQuery(List<SqlParams> pars)
        {
            SqlTransaction _Transaction = Connection.BeginTransaction();
            int res = 0;
            try
            {
                foreach (SqlParams par in pars)
                {
                    SqlCommand cmd = new SqlCommand(par.Sql, Connection, _Transaction);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();
                    if (par.Parameter != null)
                    {
                        List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(par.Parameter);
                        if (paramList.Count > 0)
                        {
                            cmd.Parameters.AddRange(paramList.ToArray());
                        }
                    }
                    
                    //执行语句
                    res += cmd.ExecuteNonQuery();

                    cmd.Dispose();

                }

                //提交事务
                _Transaction.Commit();

            }
            catch (Exception exp)
            {
                //回滚事务
                _Transaction.Rollback();
                throw exp;
            }

            _Transaction.Dispose();

            return res;
        }

        #endregion

        #region 分页

        /// <summary>
        /// 执行分页查询（在数据库分页）
        /// </summary>
        /// <param name="PageSize">每页大小</param>
        /// <param name="PageNumber">页码</param>
        /// <param name="OrderBy">排序参数。比如：“bar_sn desc,CreateTime asc”、“bar_sn asc”、“bar_sn”。</param>
        /// <param name="FromBefore">SQL语句 From 前面部分</param>
        /// <param name="FromAndBehind">SQL语句 From 本身及其后面部分</param>
        /// <param name="paramObj">参数</param>
        /// <param name="IsUseTransaction">是否使用事务</param>
        /// <returns></returns>
        public PagingData ExecutePage(int PageSize, int PageNumber, string OrderBy, string FromBefore, string FromAndBehind, object paramObj = null, bool IsUseTransaction=false)
        {
            PagingData pd = new PagingData();

            if (string.IsNullOrEmpty(OrderBy))
            {
                throw new Exception("排序参数 OrderBy 不能为空");
            }

            if (PageNumber < 1)
            {
                PageNumber = 1;
            }
            if (PageSize < 1)
            {
                PageSize = 1;
            }

            pd.pagenumber = PageNumber;
            pd.pagesize = PageSize;

            #region 获取总条数

            SqlCommand _countCMD = new SqlCommand();
            _countCMD.Connection = Connection;
            try
            {
                _countCMD.CommandText = "select count(*) num " + FromAndBehind;
                _countCMD.CommandType = CommandType.Text;
                _countCMD.Parameters.Clear();
                if (paramObj != null)
                {
                    List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(paramObj);
                    if (paramList.Count > 0)
                    {
                        _countCMD.Parameters.AddRange(paramList.ToArray());
                    }
                }
                

                if (IsUseTransaction)
                {
                    SqlTransaction tran = _countCMD.Connection.BeginTransaction();
                    try
                    {
                        pd.total = Convert.ToInt32(_countCMD.ExecuteScalar());
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
                else
                {
                    pd.total = Convert.ToInt32(_countCMD.ExecuteScalar());
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _countCMD.Dispose();
            }

            #endregion

            #region 获取数据列表

            SqlCommand _SqlCommand = new SqlCommand();
            _SqlCommand.Connection = Connection;
            try
            {
                _SqlCommand.CommandText = string.Format("select * from ({4} select row_number() over (order by {0}) as __RowNumber__,* from({4} {1} {4} )__t__{4} )__t1__{4} where __RowNumber__ > {2} and __RowNumber__ < {3} "
                    , OrderBy
                    , FromBefore + " " + FromAndBehind
                    , PageSize * (PageNumber - 1)
                    , PageSize * PageNumber + 1
                    , UtilPackage.NewLine
                );

                _SqlCommand.CommandType = CommandType.Text;
                _SqlCommand.Parameters.Clear();
                if (paramObj != null)
                {
                    List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(paramObj);
                    if (paramList.Count > 0)
                    {
                        _SqlCommand.Parameters.AddRange(paramList.ToArray());
                    }
                }
                
                using (DataTable dt = new DataTable())
                {
                    SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);

                    if (IsUseTransaction)
                    {
                        SqlTransaction tran = _SqlCommand.Connection.BeginTransaction();
                        sda.SelectCommand.Transaction = tran;

                        try
                        {
                            sda.Fill(dt);
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            throw new Exception(ex.Message);
                        }
                        finally
                        {
                            tran.Dispose();
                        }
                    }
                    else
                    {
                        sda.Fill(dt);
                    }
                    sda.Dispose();

                    pd.rows = dt;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }

            #endregion

            return pd;
        }

        /// <summary>
        /// 执行分页查询（在数据库分页）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PageSize">每页大小</param>
        /// <param name="PageNumber">页码</param>
        /// <param name="OrderBy">排序参数。比如：“bar_sn desc,CreateTime asc”、“bar_sn asc”、“bar_sn”。</param>
        /// <param name="FromBefore">SQL语句 From 前面部分</param>
        /// <param name="FromAndBehind">SQL语句 From 本身及其后面部分</param>
        /// <param name="paramObj">参数</param>
        /// <param name="IsUseTransaction">是否使用事务</param>
        /// <returns></returns>
        public PagingData<T> ExecutePage<T>(int PageSize, int PageNumber, string OrderBy, string FromBefore, string FromAndBehind, object paramObj = null, bool IsUseTransaction = false) where T : class
        {
            PagingData pData = ExecutePage(PageSize, PageNumber, OrderBy, FromBefore, FromAndBehind, paramObj, IsUseTransaction);
            return new PagingData<T>
            {
                pagenumber = pData.pagenumber,
                pagesize = pData.pagesize,
                total = pData.total,
                rows = pData.rows.ToList<T>()
            };
        }




        #endregion

        #region ExecuteReader

        /// <summary>
        /// 执行SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">命令类型</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string sql, object paramObj = null, CommandType cmdType = CommandType.Text)
        {
            SqlCommand _SqlCommand = CreateCommand(sql, paramObj, cmdType) as SqlCommand;
            try
            {
                return _SqlCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句或存储过程（返回查询所返回的结果集中的第一行第一列）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">命令类型</param>
        /// <returns>返回查询所返回的结果集中的第一行第一列</returns>
        public object ExecuteScalar(string sql, object paramObj = null, CommandType cmdType = CommandType.Text)
        {
            SqlCommand _SqlCommand = CreateCommand(sql, paramObj, cmdType) as SqlCommand;
            try
            {
                return _SqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion

        #region ExecuteScalarString

        /// <summary>
        /// 执行SQL语句或存储过程（返回查询所返回的结果集中的第一行第一列）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramObj">参数</param>
        /// <param name="cmdType">命令类型</param>
        /// <returns>返回查询所返回的结果集中的第一行第一列</returns>
        public string ExecuteScalarString(string sql, object paramObj = null, CommandType cmdType = CommandType.Text)
        {
            return Convert.ToString(ExecuteScalar(sql, paramObj, cmdType));
        }
        #endregion

        #region 实体操作-添加数据

        /// <summary>
        /// 实体操作-添加数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="paramObj">待添加的实体</param>
        public bool Add(string table_name, object paramObj)
        {
            if (!CacheHelper.IsExistTable(table_name)) {
                throw new Exception(string.Format("【添加数据实体】表“{0}”不存在", table_name));
            };
            DotNetDAL.Entity.DbStruct.DbStructColumnCache cahceData = CacheHelper.GetDbStructColumns(table_name);
            DbActionParam<System.Data.SqlClient.SqlParameter> parObj = SqlServerParamHelper.CreateSqlEntityParameters(cahceData, paramObj);

            string sql = string.Format("insert into {0}({1}) values({2})"
                    ,table_name
                    ,string.Join(",", parObj.column)
                    ,"@"+ string.Join(",@", parObj.column)
                );
           return ExecuteNonQuery(sql, parObj.paramList.ToArray()) > 0;

        }
        #endregion

        #region 实体操作-更新数据

        /// <summary>
        /// 实体操作-更新数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="updateFieldParam">待更新的实体</param>
        /// <param name="whereSQL">where条件SQL</param>
        /// <param name="whereParamObj">where条件SQL参数</param>
        /// <returns></returns>
        public bool Update(string table_name, object updateFieldParam, string whereSQL,object whereParamObj = null)
        {
            whereSQL = whereSQL == null ? whereSQL : whereSQL.Trim();
            if (string.IsNullOrEmpty(whereSQL))
            {
                throw new Exception(" 【更新数据实体】where 条件SQL 不能为空");
            };
            if (!CacheHelper.IsExistTable(table_name))
            {
                throw new Exception(string.Format("【更新数据实体】表“{0}”不存在", table_name));
            };

            //生成 where 条件的字段和参数
            List<System.Data.SqlClient.SqlParameter> paramList = new List<SqlParameter>();

            //生成 where 条件的参数
            paramList.AddRange(SqlServerParamHelper.CreateSqlParameters(whereParamObj));

            DotNetDAL.Entity.DbStruct.DbStructColumnCache cahceData = CacheHelper.GetDbStructColumns(table_name);
            string updatePrefix = "u";
            //生成更新的字段和参数
            DbActionParam<System.Data.SqlClient.SqlParameter> updateParamObj =
                SqlServerParamHelper.CreateSqlEntityParameters(cahceData, updateFieldParam, updatePrefix);

            // 生成更新的参数
            paramList.AddRange(updateParamObj.paramList);

            List<string> ucols = new List<string>();
            foreach(string pcol in updateParamObj.column)
            {
                ucols.Add(string.Format("{0}=@{1}{0}", pcol, updatePrefix));
            }

            if(ucols.Count < 1)
            {
                throw new Exception("获取不到需要更新的实体的表字段");
            }

            string sql = string.Format("update {0} set {1} {2}"
                    , table_name
                    , string.Join(",", ucols)
                    , (whereSQL.ToLower().StartsWith("where") ?" ":" where ")+ whereSQL
                );

            return ExecuteNonQuery(sql, paramList.ToArray()) > 0;
        }

        /// <summary>
        /// 实体操作-更新数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="updateFieldParam">待更新的实体</param>
        /// <param name="whereParam">where条件参数</param>
        /// <returns></returns>
        public bool Update(string table_name, object updateFieldParam, WhereParameter whereParam) {
            return Update(table_name, updateFieldParam, whereParam.SQL, whereParam.Parameter);
        }

        #endregion

        #region tool

        /// <summary>
        /// DataRow 转成 对象T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <returns></returns>
        public static T ToObject<T>(DataRow row) where T : class
        {
            return row.ToObject<T>();
        }
        /// <summary>
        /// DataRow[] 转成 List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(DataRow[] rows) where T : class
        {
            return rows.ToList<T>();
        }
        /// <summary>
        /// 将实体集合转换为DataTable
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="list">实体集合</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(List<T> list)
        {
            return list.ToDataTable();
        }
        /// <summary>
        /// 排序。排序字符串。像SQL 的order by 后面的内容。如：“AA”,“AA asc”，“AA desc,BB”
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="OrderBy">排序字符串。像SQL 的order by 后面的内容。如：“AA”,“AA asc”，“AA desc,BB”</param>
        /// <returns></returns>
        public static List<T> SortBy<T>(List<T> list, string OrderBy) where T : class
        {
            return list.SortBy(OrderBy);
        }
        /// <summary>
        /// DataSet 转成 List
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static List<List<Dictionary<string, object>>> ToDictionaryList(DataSet ds)
        {
            return ds.ToDictionaryList();
        }
        /// <summary>
        /// 转换为字典集合
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> ToDictionaryList(DataTable dt)
        {
            return dt.ToDictionaryList();
        }

        /// <summary>
        /// DataTable 转换为字典
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="majorKeyColumn">主键列名</param>
        /// <returns></returns>
        public static Dictionary<string, T> ToDictionary<T>(DataTable dt, string majorKeyColumn) where T : class, new()
        {
            return dt.ToDictionary<T>(majorKeyColumn);
        }

        /// <summary>
        /// DataTable 转成 List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(DataTable dt) where T : class
        {
            return dt.ToList<T>();
        }
        /// <summary>
        /// DataTable 某列转成 String List
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ColumnName"></param>
        /// <returns></returns>
        public static List<string> ToStringList(DataTable dt, string ColumnName)
        {
            return dt.ToStringList(ColumnName);
        }
        /// <summary>
        /// 生成分页数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="PageSize">每页大小</param>
        /// <param name="pageNumber">页码</param>
        /// <returns></returns>
        public static PagingData ToPagingData(DataTable dt, int PageSize, int pageNumber)
        {
            return dt.ToPagingData(PageSize, pageNumber);
        }

        /// <summary>
        /// 获取第一行数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetFirstOrDefault(DataTable dt)
        {
            return dt.GetFirstOrDefault();
        }

        #endregion

        #region 生成参数

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="paramObj">参数对象</param>
        /// <returns></returns>
        public List<SqlParameter> CreateParameter(object paramObj) {
            //生成 where 条件的参数
            return SqlServerParamHelper.CreateSqlParameters(paramObj);
        }

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, object value)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new Exception("参数 name 不能为空");
            }

            if (value == null)
            {
                return new SqlParameter(name, DBNull.Value);
            }
            else if (value is string)
            {
                string _value = Convert.ToString(value);
                if (string.IsNullOrEmpty(_value))
                {
                    return new SqlParameter(name, DBNull.Value);
                }
            }

            return new SqlParameter(name, value);
        }

        #endregion


        #region 执行存储过程


        #region ExecuteStoredProcedure

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        private List<ProcOutputParameter> GetProc(string procName)
        {
            string sql = @"
select col.name par_name,t.name par_type,col.length par_length
from syscolumns col
inner join sysobjects obj on col.id = obj.id AND obj.xtype = 'P' AND obj.status >= 0  
left join systypes t on col.xusertype = t.xusertype 
where col.isoutparam=1 and obj.name=@procName
";
            SqlCommand _SqlCommand = CreateCommand(sql, new {
                procName= procName
            }, CommandType.Text) as SqlCommand;
            try
            {
                using (DataTable dt = new DataTable())
                {
                    SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
                    sda.Fill(dt);

                    sda.Dispose();

                    List < ProcOutputParameter > resList = new List<ProcOutputParameter>();
                    foreach (DataRow dr in dt.Rows) {
                        resList.Add(new ProcOutputParameter() {
                            procParamName = Convert.ToString(dr["par_name"]),
                            procParamType = Convert.ToString(dr["par_type"]),
                            procParamLength = Convert.ToInt32(dr["par_length"]),
                        });
                    }

                    return resList;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }

        }

        private SqlDbType GetSqlDbType(string sqlDbType) {
            switch (sqlDbType) {
                case "bigint": return SqlDbType.BigInt;
                case "binary": return SqlDbType.Binary;
                case "bit": return SqlDbType.Bit;
                case "char": return SqlDbType.Char;
                case "date": return SqlDbType.Date;
                case "datetime": return SqlDbType.DateTime;
                case "datetime2": return SqlDbType.DateTime2;
                case "datetimeoffset": return SqlDbType.DateTimeOffset;
                case "decimal": return SqlDbType.Decimal;
                case "float": return SqlDbType.Float;
                //case "geography": return SqlDbType.g;
                //case "geometry": return SqlDbType.;
                //case "hierarchyid": return SqlDbType.;
                case "image": return SqlDbType.Image;
                case "int": return SqlDbType.Int;
                case "money": return SqlDbType.Money;
                case "nchar": return SqlDbType.NChar;
                case "ntext": return SqlDbType.NText;
                //case "numeric": return SqlDbType.;
                case "nvarchar": return SqlDbType.NVarChar;
                case "real": return SqlDbType.Real;
                case "smalldatetime": return SqlDbType.SmallDateTime;
                case "smallint": return SqlDbType.SmallInt;
                case "smallmoney": return SqlDbType.SmallMoney;
                //case "sql_variant": return SqlDbType.sq;
                //case "sysname": return SqlDbType.s;
                case "text": return SqlDbType.Text;
                case "time": return SqlDbType.Time;
                case "timestamp": return SqlDbType.Timestamp;
                case "tinyint": return SqlDbType.TinyInt;
                case "uniqueidentifier": return SqlDbType.UniqueIdentifier;
                case "varbinary": return SqlDbType.VarBinary;
                case "varchar": return SqlDbType.VarChar;
                case "xml": return SqlDbType.Xml;
                default:return SqlDbType.VarChar;
            }
            
        }

        private bool IsNeedSetLength(string sqlDbType) {
            switch (sqlDbType)
            {
                case "char": 
                case "nchar": 
                case "nvarchar":
                case "varchar": { return true; }
                default: return false;
            }
        }

        #endregion


        #region ExecuteProc

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="procReturn">返回值</param>
        /// <param name="procOutputs">输出参数</param>
        /// <param name="paramObj">参数</param>
        /// <returns></returns>
        public int ExecuteProc(string procName, out int procReturn, out Dictionary<string, object> procOutputs, object paramObj = null)
        {
            SqlCommand _SqlCommand = CreateCommand(procName, paramObj, CommandType.StoredProcedure) as SqlCommand;

            _SqlCommand.Parameters.Add("@__ReturnValue__", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            List<ProcOutputParameter> procOutputList = GetProc(procName);
            foreach (ProcOutputParameter procOutputParameter in procOutputList)
            {
                if (IsNeedSetLength(procOutputParameter.procParamType))
                {
                    _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType), procOutputParameter.procParamLength)
                        .Direction = ParameterDirection.Output;
                }
                else {
                    _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType))
                        .Direction = ParameterDirection.Output;
                }

            }



            int res = _SqlCommand.ExecuteNonQuery();

            //获取参数值
            procReturn = Convert.ToInt32(_SqlCommand.Parameters["@__ReturnValue__"].Value);
            procOutputs = new Dictionary<string, object>();
            foreach (ProcOutputParameter procOutputParameter in procOutputList)
            {
                procOutputs[procOutputParameter.procParamName.Replace("@","")] = _SqlCommand.Parameters[procOutputParameter.procParamName].Value;
            }

            return res;
        }

        #endregion

        #region ExecuteProcDataTable

        /// <summary>
        /// 执行存储过程，返回数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="procReturn"></param>
        /// <param name="procOutputs"></param>
        /// <param name="paramObj"></param>
        /// <returns></returns>
        public DataTable ExecuteProcDataTable(string procName, out int procReturn, out Dictionary<string, object> procOutputs, object paramObj = null)
        {
            SqlCommand _SqlCommand = CreateCommand(procName, paramObj, CommandType.StoredProcedure) as SqlCommand;
            try
            {

                _SqlCommand.Parameters.Add("@__ReturnValue__", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                List<ProcOutputParameter> procOutputList = GetProc(procName);
                foreach (ProcOutputParameter procOutputParameter in procOutputList)
                {
                    if (IsNeedSetLength(procOutputParameter.procParamType))
                    {
                        _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType), procOutputParameter.procParamLength)
                            .Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType))
                            .Direction = ParameterDirection.Output;
                    }

                }


                using (DataTable dt = new DataTable())
                {
                    SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
                    sda.Fill(dt);

                    

                    //获取参数值
                    procReturn = Convert.ToInt32(_SqlCommand.Parameters["@__ReturnValue__"].Value);
                    procOutputs = new Dictionary<string, object>();
                    foreach (ProcOutputParameter procOutputParameter in procOutputList)
                    {
                        procOutputs[procOutputParameter.procParamName.Replace("@","")] = _SqlCommand.Parameters[procOutputParameter.procParamName].Value;
                    }


                    sda.Dispose();
                    return dt;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion

        #region ExecuteProcDataSet

        /// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="procReturn"></param>
        /// <param name="procOutputs"></param>
        /// <param name="paramObj"></param>
        /// <returns></returns>
        public DataSet ExecuteProcDataSet(string procName, out int procReturn, out Dictionary<string, object> procOutputs, object paramObj = null)
        {
            SqlCommand _SqlCommand = CreateCommand(procName, paramObj, CommandType.StoredProcedure) as SqlCommand;
            try
            {
                _SqlCommand.Parameters.Add("@__ReturnValue__", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                List<ProcOutputParameter> procOutputList = GetProc(procName);
                foreach (ProcOutputParameter procOutputParameter in procOutputList)
                {
                    if (IsNeedSetLength(procOutputParameter.procParamType))
                    {
                        _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType), procOutputParameter.procParamLength)
                            .Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        _SqlCommand.Parameters.Add(procOutputParameter.procParamName, GetSqlDbType(procOutputParameter.procParamType))
                            .Direction = ParameterDirection.Output;
                    }

                }

                SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);

                    //获取参数值
                    procReturn = Convert.ToInt32(_SqlCommand.Parameters["@__ReturnValue__"].Value);
                    procOutputs = new Dictionary<string, object>();
                    foreach (ProcOutputParameter procOutputParameter in procOutputList)
                    {
                        procOutputs[procOutputParameter.procParamName.Replace("@","")] = _SqlCommand.Parameters[procOutputParameter.procParamName].Value;
                    }

                    sda.Dispose();
                    return ds;
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                _SqlCommand.Dispose();
            }
        }

        #endregion





        #endregion


        #region 执行事务

        /// <summary>
        /// 执行事务。  ExecuteTransaction( tran => {});
        /// </summary>
        /// <param name="action"></param>
        public void ExecuteTransaction(Action<ITransactor> action)
        {
            //开始事务
            SqlTransaction tran = Connection.BeginTransaction();
            SqlServerTransactor transactor = new SqlServerTransactor(Connection, tran);
            try
            {
                // 委托处理动作事件
                action(transactor);
                //提交事务
                tran.Commit();
            }
            catch (Exception exp)
            {
                //异常回滚
                tran.Rollback();
                throw exp;
            }
            finally
            {
                tran.Dispose();
            }
        }

        #endregion

    }
}
